package com.oliver.proxy.midjourney.application.service;

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.http.HttpEntity;
import org.apache.http.client.methods.CloseableHttpResponse;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.entity.mime.MultipartEntityBuilder;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;
import org.apache.http.util.EntityUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;

import javax.annotation.PostConstruct;
import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.sql.*;
import java.util.*;
import java.util.Date;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.atomic.AtomicBoolean;
import java.util.stream.Collectors;

import com.oliver.proxy.midjourney.infrastructure.mapper.FinalProdPicMapper;

@Slf4j
@Service
public class Upload2KuaXunProcessingService {
    private static final String AUTH_URL = "https://erp.kuaxun.net/request/auth/oauth2/token?grant_type=password&scope=all";
    private static final String AUTH_HEADER = "Basic a3VheHVuX2xpc3Rpbmc6a3VheHVuX2xpc3Rpbmc=";
    private static final String USERNAME = "13811111111";
    private static final String PASSWORD = "123456";
    private final ObjectMapper objectMapper = new ObjectMapper();
    private String accessToken;
    private final AtomicBoolean isProcessing = new AtomicBoolean(false);

    private final FinalProdPicMapper finalProdPicMapper;

    private final String templatePath;
    private final String outputPath;

    @Autowired
    public Upload2KuaXunProcessingService(FinalProdPicMapper finalProdPicMapper) {
        this.finalProdPicMapper = finalProdPicMapper;

        // 设置模板和输出路径
        String basePath = System.getProperty("user.dir");
        this.templatePath = basePath + "/proxy/src/main/java/com/oliver/proxy/midjourney/application/service/demo/template.xlsx";
        this.outputPath = basePath + "/proxy/src/main/java/com/oliver/proxy/midjourney/application/service/demo/.temp.xlsx";
    }

    @PostConstruct
    public void init() {
        log.info("Initializing Upload2KuaXunProcessingService");
        CompletableFuture.runAsync(this::processUpload);
    }

    @Scheduled(fixedRate = 10000) // 每10秒执行一次
    public void checkAndRestartProcessing() {
        if (!isProcessing.get()) {
            CompletableFuture.runAsync(this::processUpload);
        }
    }

    private void processUpload() {
        if (isProcessing.compareAndSet(false, true)) {
            try {
                // 1. 从数据库读取数据
                List<Map<String, Object>> allRecords = finalProdPicMapper.selectUnprocessedRecords();

                if (allRecords.isEmpty()) {
                    return;
                }

                // 2. 处理数据
                List<Map<String, Object>> processedData = processData(allRecords);

                if (processedData.isEmpty()) {
//                    log.info("No data to export after processing");
                    return;
                }

                try {
                    // 3. 导出数据到Excel
                    exportToExcel(processedData, templatePath, outputPath);
                    
                    log.info("Data processing completed, exported to {}", outputPath);

                    // 4. 上传文件到ERP系统
                     ensureValidToken();
                     uploadToErp(outputPath);

                    // 5. 只有成功导出表格后才更新记录状态
                    // 找出实际被处理并写入Excel的记录
                    Set<String> processedAttr2Values = processedData.stream()
                        .map(record -> record.get("SPU编码").toString())
                        .collect(Collectors.toSet());
                    
                    // 只更新那些SPU编码在已处理集合中的记录
                    List<Map<String, Object>> recordsToUpdate = allRecords.stream()
                        .filter(record -> {
                            String attr2 = record.get("attritube_2") != null ? 
                                record.get("attritube_2").toString() : "";
                            return processedAttr2Values.contains(attr2);
                        })
                        .collect(Collectors.toList());
                    
                    updateProcessedRecords(recordsToUpdate);
                    
                } catch (Exception e) {
                    log.error("Error exporting to Excel: {}", e.getMessage());
                    // 如果导出失败，不更新数据库记录
                }

            } catch (Exception e) {
                log.error("Error during KuaXun upload process", e);
            } finally {
                isProcessing.set(false);
            }
        } else {
            log.info("KuaXun upload process already running");
        }
    }

    private void updateProcessedRecords(List<Map<String, Object>> records) {
        try {
            if (records.isEmpty()) {
                return;
            }

            // 提取所有记录的ID
            List<Long> ids = records.stream()
                    .map(record -> {
                        Object id = record.get("pic_id");
                        if (id instanceof Number) {
                            return ((Number) id).longValue();
                        }
                        return null;
                    })
                    .filter(Objects::nonNull)
                    .collect(Collectors.toList());

            if (ids.isEmpty()) {
                return;
            }

            // 使用MyBatis更新已处理记录
            int updatedRows = finalProdPicMapper.updateProcessedRecords(ids);
            log.info("Updated {} records as processed", updatedRows);

        } catch (Exception e) {
            log.error("Error updating processed records", e);
        }
    }

    private void ensureValidToken() throws IOException {
        if (accessToken == null) {
            try (CloseableHttpClient httpClient = HttpClients.createDefault()) {
                HttpPost authPost = new HttpPost(AUTH_URL);
                authPost.setHeader("Authorization", AUTH_HEADER);
                authPost.setHeader("Content-Type", "multipart/form-data; boundary=----WebKitFormBoundarylhN5J1NQ5P19ftvA");

                MultipartEntityBuilder builder = MultipartEntityBuilder.create();
                builder.setBoundary("----WebKitFormBoundarylhN5J1NQ5P19ftvA");
                builder.addTextBody("username", USERNAME);
                builder.addTextBody("password", PASSWORD);
                builder.addTextBody("remember-me", "false");

                HttpEntity multipartEntity = builder.build();
                authPost.setEntity(multipartEntity);

                try (CloseableHttpResponse response = httpClient.execute(authPost)) {
                    int statusCode = response.getStatusLine().getStatusCode();
                    if (statusCode != 200) {
                        throw new IOException("Authentication failed with status code: " + statusCode);
                    }

                    String responseBody = EntityUtils.toString(response.getEntity());
                    JsonNode jsonResponse = objectMapper.readTree(responseBody);

                    if (!jsonResponse.has("access_token")) {
                        throw new IOException("Invalid authentication response: missing access_token");
                    }

                    accessToken = jsonResponse.get("access_token").asText();
                    log.info("Successfully obtained new access token");
                }
            }
        }
    }

    private List<Map<String, Object>> processData(List<Map<String, Object>> allRecords) {
        // Get unique attritube_2 values from allRecords
        Set<String> uniqueAttr2Values = allRecords.stream()
                .map(record -> record.get("attritube_2") != null ? record.get("attritube_2").toString() : "")
                .filter(s -> !s.isEmpty())
                .collect(Collectors.toSet());

        // Get the required numbers for each group from the database
        List<Map<String, Object>> requiredNumbersList = finalProdPicMapper.getRequiredNumbers(new ArrayList<>(uniqueAttr2Values));
        
        // Convert the list to a map of extCode -> required number
        Map<String, Integer> requiredNumbers = requiredNumbersList.stream()
                .collect(Collectors.toMap(
                        record -> record.get("extCode").toString(),
                        record -> {
                            try {
                                return Integer.parseInt(record.get("num").toString());
                            } catch (NumberFormatException e) {
                                log.warn("Invalid number format in task_list.attritube_1 for extCode: {}", record.get("extCode"));
                                return null;
                            }
                        },
                        (existing, replacement) -> existing
                ));

        // 按attribute_2分组
        Map<String, List<Map<String, Object>>> groupedByAttr2 = allRecords.stream()
                .collect(Collectors.groupingBy(record -> record.get("attritube_2") != null ? record.get("attritube_2").toString() : ""))
                .entrySet()
                .stream()
                .filter(x -> {
                    Integer requiredNum = requiredNumbers.get(x.getKey());
                    return requiredNum != null && x.getValue().size() == requiredNum;
                })
                .collect(Collectors.toMap(
                        Map.Entry::getKey,
                        Map.Entry::getValue
                ));

        List<Map<String, Object>> result = new ArrayList<>();

        // 处理每个组
        for (Map.Entry<String, List<Map<String, Object>>> entry : groupedByAttr2.entrySet()) {
            List<Map<String, Object>> group = entry.getValue();

            // 1. 找出该组中类型为"预览图"的记录
            List<Map<String, Object>> previewImages = group.stream()
                    .filter(record -> "预览图".equals(record.get("pic_type")))
                    .collect(Collectors.toList());

            // 2. 找出该组中类型为"场景图"的记录并按pic_priority排序
            List<Map<String, Object>> sceneImages = group.stream()
                    .filter(record -> "场景图".equals(record.get("pic_type")))
                    .sorted((a, b) -> {
                        Integer priorityA = a.get("pic_priority") instanceof Number ?
                            ((Number)a.get("pic_priority")).intValue() : Integer.MAX_VALUE;
                        Integer priorityB = b.get("pic_priority") instanceof Number ?
                            ((Number)b.get("pic_priority")).intValue() : Integer.MAX_VALUE;
                        return priorityA.compareTo(priorityB);
                    })
                    .collect(Collectors.toList());

            // 3. 找出该组中类型为"详情图"的记录，并按pic_priority排序
            List<Map<String, Object>> detailImages = group.stream()
                    .filter(record -> "详情图".equals(record.get("pic_type")))
                    .sorted((a, b) -> {
                        Integer priorityA = a.get("pic_priority") instanceof Number ?
                            ((Number)a.get("pic_priority")).intValue() : Integer.MAX_VALUE;
                        Integer priorityB = b.get("pic_priority") instanceof Number ?
                            ((Number)b.get("pic_priority")).intValue() : Integer.MAX_VALUE;
                        return priorityA.compareTo(priorityB);
                    })
                    .collect(Collectors.toList());

            // 4. 找出该组中类型为"尺寸图"的记录
            List<Map<String, Object>> sizeImages = group.stream()
                    .filter(record -> "尺寸图".equals(record.get("pic_type")))
                    .collect(Collectors.toList());

            // 根据预览图数量创建记录
            if (!previewImages.isEmpty()) {
                for (Map<String, Object> previewImage : previewImages) {
                    // 创建基本记录 - 使用组内第一条记录作为基础信息
                    Map<String, Object> baseRecord = new HashMap<>(group.get(0));

                    // 整合图片信息

                    // 1. 产品图片（主图） - 放预览图
                    baseRecord.put("产品图片（主图）", previewImage.get("pic_url"));

                    // 处理SKU编码：attritube_2 + "-" + 预览图文件名中"预览图"后的部分
                    String attritube2 = previewImage.get("attritube_2").toString();
                    String title = previewImage.get("title").toString();
                    String picUrl = previewImage.get("pic_url").toString();

                    // 从图片URL中提取文件名
                    String fileName = picUrl.substring(picUrl.lastIndexOf("/") + 1);
                    // 找到"预览图"在文件名中的位置，并提取其后的部分
                    int previewIndex = fileName.indexOf("预览图");
                    String skuSuffix = "";
                    if (previewIndex != -1 && previewIndex + 3 < fileName.length()) {
                        skuSuffix = fileName.substring(previewIndex + 3);
                        // 如果后缀以文件扩展名结尾，去掉扩展名
                        int extensionIndex = skuSuffix.lastIndexOf(".");
                        if (extensionIndex != -1) {
                            skuSuffix = skuSuffix.substring(0, extensionIndex);
                        }
                    }

                    // 组合SKU编码
                    String skuCode = attritube2 + "-" + skuSuffix;
                    baseRecord.put("SKU编码", skuCode);
                    baseRecord.put("SPU编码", attritube2);
                    baseRecord.put("导入方式", "新增");
                    // 处理英文标题，限制最大长度为250字符
                    if (title.length() > 250) {
                        title = title.substring(0, 250);
                    }
                    baseRecord.put("英文标题", title);
                    if (attritube2.startsWith("M")) {
                        baseRecord.put("产品中文名称（25个字内）", "沙滩巾");
                        baseRecord.put("是否主SKU", "Z1".equals(skuSuffix) ? "是" : "否");
                        baseRecord.put("尺寸英文名称", "Z1".equals(skuSuffix) ? "31.5*63 in (80*160 cm)" : "35.4*70.9 in (90*180 cm)");
                        baseRecord.put("包装后重量(kg)", "Z1".equals(skuSuffix) ? "0.35" : "0.45");
                        baseRecord.put("包装后长度(cm)", "25");
                        baseRecord.put("包装后宽度(cm)", "20");
                        baseRecord.put("包装后高度(cm)", "5");
                        baseRecord.put("产品分组\n" +
                                "多级分组用\">\"连接，多个英文逗号隔开", "沙滩巾");
                        baseRecord.put("颜色中文名称", "多种颜色");
                        baseRecord.put("颜色英文名称", "Multicolor");
                        baseRecord.put("个数中文名称", "一个装");
                        baseRecord.put("个数英文名称", "1 piece");
                    }

                    // 2. 产品图片1~11 - 放场景图（按优先级），特殊处理产品图片3
                    StringBuilder otherImages = new StringBuilder();
                    boolean hasSizeImage = !sizeImages.isEmpty();

                    // 处理产品图片
                    int productImageIndex = 1; // 从产品图片1开始

                    // 处理前两张场景图（产品图片1和2）
                    for (int i = 0; i < Math.min(2, sceneImages.size()); i++) {
                        baseRecord.put("产品图片" + productImageIndex, sceneImages.get(i).get("pic_url").toString());
                        productImageIndex++;
                    }

                    // 处理产品图片3 - 放尺寸图或场景图
                    if (hasSizeImage) {
                        baseRecord.put("产品图片3", sizeImages.get(0).get("pic_url").toString());
                    } else if (sceneImages.size() > 2) {
                        baseRecord.put("产品图片3", sceneImages.get(2).get("pic_url").toString());
                    }
                    productImageIndex = 4; // 继续从产品图片4开始

                    // 处理剩余场景图（从索引2或3开始，取决于是否有尺寸图）
                    int startIndex = hasSizeImage ? 2 : 3; // 如果放了尺寸图，从场景图2开始；否则从场景图3开始

                    for (int i = startIndex; i < sceneImages.size(); i++) {
                        if (productImageIndex <= 11) { // 最多填充到产品图片11
                            baseRecord.put("产品图片" + productImageIndex, sceneImages.get(i).get("pic_url").toString());
                            productImageIndex++;
                        } else {
                            // 超过11张的场景图放入其它图片，用逗号分隔
                            if (otherImages.length() > 0) {
                                otherImages.append(",");
                            }
                            otherImages.append(sceneImages.get(i).get("pic_url").toString());
                        }
                    }

                    // 3. 其它图片多个图片用英文逗号隔开 - 放剩余场景图
                    if (otherImages.length() > 0) {
                        baseRecord.put("其它图片\n多个图片用英文逗号隔开", otherImages.toString());
                    }

                    // 4. 产品描述 - 放详情图，用逗号分隔
                    if (!detailImages.isEmpty()) {
                        StringBuilder detailUrls = new StringBuilder();
                        for (Map<String, Object> detailImage : detailImages) {
                            if (detailUrls.length() > 0) {
                                detailUrls.append(",");
                            }
                            detailUrls.append(detailImage.get("pic_url"));
                        }
                        baseRecord.put("产品描述", detailUrls.toString());
                    }

                    result.add(baseRecord);
                }
            }
        }

//        log.info("Processed {} records into {} export records", allRecords.size(), result.size());
        return result;
    }

    private void exportToExcel(List<Map<String, Object>> data, String templatePath, String outputPath)
            throws IOException {
        // 读取模板文件获取表头和格式
        List<String> headers = new ArrayList<>();
        Workbook templateWorkbook = null;

        try (FileInputStream fis = new FileInputStream(templatePath)) {
            templateWorkbook = new XSSFWorkbook(fis);
            Sheet templateSheet = templateWorkbook.getSheetAt(0);
            Row headerRow = templateSheet.getRow(1); // 使用第二行作为数据对应的列标题

            if (headerRow != null) {
                int lastCell = headerRow.getLastCellNum();
                for (int i = 0; i < lastCell; i++) {
                    Cell cell = headerRow.getCell(i);
                    if (cell != null) {
                        headers.add(cell.getStringCellValue());
                    } else {
                        headers.add(""); // 对于可能的空单元格，添加空字符串
                    }
                }
            }
        }

        // 创建新工作簿
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("数据");

            // 复制模板的表头格式（包括两行表头和合并单元格）
            if (templateWorkbook != null) {
                Sheet templateSheet = templateWorkbook.getSheetAt(0);

                // 复制前两行（表头）
                for (int i = 0; i <= 1; i++) {
                    Row templateRow = templateSheet.getRow(i);
                    if (templateRow != null) {
                        Row newRow = sheet.createRow(i);
                        // 设置行高与模板相同
                        newRow.setHeight(templateRow.getHeight());

                        // 复制单元格内容和样式
                        for (int j = 0; j < templateRow.getLastCellNum(); j++) {
                            Cell templateCell = templateRow.getCell(j);
                            if (templateCell != null) {
                                Cell newCell = newRow.createCell(j);

                                // 复制单元格值
                                switch (templateCell.getCellType()) {
                                    case STRING:
                                        newCell.setCellValue(templateCell.getStringCellValue());
                                        break;
                                    case NUMERIC:
                                        newCell.setCellValue(templateCell.getNumericCellValue());
                                        break;
                                    case BOOLEAN:
                                        newCell.setCellValue(templateCell.getBooleanCellValue());
                                        break;
                                    case FORMULA:
                                        newCell.setCellValue(templateCell.getCellFormula());
                                        break;
                                    default:
                                        break;
                                }

                                // 复制单元格样式
                                CellStyle newStyle = workbook.createCellStyle();
                                CellStyle templateStyle = templateCell.getCellStyle();
                                newStyle.cloneStyleFrom(templateStyle);
                                newCell.setCellStyle(newStyle);
                            }
                        }
                    }
                }

                // 复制合并单元格
                for (int i = 0; i < templateSheet.getNumMergedRegions(); i++) {
                    CellRangeAddress mergedRegion = templateSheet.getMergedRegion(i);
                    // 仅复制表头部分的合并单元格（前两行）
                    if (mergedRegion.getFirstRow() <= 1 && mergedRegion.getLastRow() <= 1) {
                        sheet.addMergedRegion(mergedRegion);
                    }
                }

                // 设置列宽与模板相同
                for (int i = 0; i < headers.size(); i++) {
                    sheet.setColumnWidth(i, templateSheet.getColumnWidth(i));
                }
            }

            // 写入数据（从第3行开始，即索引2）
            for (int i = 0; i < data.size(); i++) {
                Row dataRow = sheet.createRow(i + 2); // 从第3行开始
                Map<String, Object> record = data.get(i);

                for (int j = 0; j < headers.size(); j++) {
                    String header = headers.get(j);
                    if (header.isEmpty()) continue; // 跳过空表头

                    Cell cell = dataRow.createCell(j);

                    Object value = record.get(header);
                    if (value != null) {
                        if (value instanceof Number) {
                            cell.setCellValue(((Number) value).doubleValue());
                        } else if (value instanceof Date) {
                            cell.setCellValue((Date) value);
                        } else {
                            cell.setCellValue(value.toString());
                        }
                    }
                }
            }

            // 保存工作簿
            try (FileOutputStream fos = new FileOutputStream(outputPath)) {
                workbook.write(fos);
            }

            // 关闭模板工作簿
            if (templateWorkbook != null) {
                templateWorkbook.close();
            }
        }
    }

    private void uploadToErp(String filePath) {
        String url = "https://erp.kuaxun.net/request/listing/pl/import/product";
        String boundary = "----WebKitFormBoundaryOGWAH4zYMnpGUXer";
        File file = new File(filePath);

        try {
            URL obj = new URL(url);
            HttpURLConnection con = (HttpURLConnection) obj.openConnection();

            // 设置请求方法和头部信息
            con.setRequestMethod("POST");
            con.setDoOutput(true);
            con.setRequestProperty("Content-Type", "multipart/form-data; boundary=" + boundary);
            con.setRequestProperty("Authorization", "BEARER " + accessToken);
            con.setRequestProperty("User-Agent", "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/134.0.0.0 Safari/537.36");
            con.setRequestProperty("Accept", "application/json, text/plain, */*");
            con.setRequestProperty("Referer", "https://erp.kuaxun.net/");

            // 构建multipart请求体
            try (OutputStream os = con.getOutputStream();
                 PrintWriter writer = new PrintWriter(new OutputStreamWriter(os, "UTF-8"), true)) {

                writer.append("--").append(boundary).append("\r\n");
                writer.append("Content-Disposition: form-data; name=\"file\"; filename=\"")
                      .append(file.getName()).append("\"\r\n");
                writer.append("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet\r\n");
                writer.append("\r\n");
                writer.flush();

                // 写入文件内容
                try (FileInputStream fis = new FileInputStream(file)) {
                    byte[] buffer = new byte[4096];
                    int bytesRead;
                    while ((bytesRead = fis.read(buffer)) != -1) {
                        os.write(buffer, 0, bytesRead);
                    }
                    os.flush();
                }

                writer.append("\r\n");
                writer.append("--").append(boundary).append("--").append("\r\n");
                writer.flush();
            }

            // 获取响应
            int responseCode = con.getResponseCode();
            try (BufferedReader in = new BufferedReader(new InputStreamReader(
                    responseCode >= 400 ? con.getErrorStream() : con.getInputStream()))) {
                String inputLine;
                StringBuilder response = new StringBuilder();
                while ((inputLine = in.readLine()) != null) {
                    response.append(inputLine);
                }
                log.info("Upload Response Code: {}", responseCode);
                log.info("Upload Response: {}", response.toString());
            }

        } catch (Exception e) {
            log.error("Error uploading file to ERP", e);
        } finally {
            // 删除临时文件
            try {
                if (file.exists()) {
                    boolean deleted = file.delete();
                    if (deleted) {
                        log.info("Temporary file deleted successfully: {}", filePath);
                    } else {
                        log.warn("Failed to delete temporary file: {}", filePath);
                    }
                }
            } catch (Exception e) {
                log.error("Error deleting temporary file: {}", e.getMessage());
            }
        }
    }
} 